package com.apes.scm.stock.transfer.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.stock.transfer.model.Pick;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author xul
 * @create 2018-01-23 16:07
 */
public interface PickRepository extends CustomRepository<Pick, String> {

    List<Pick> findByOriginAndState(String origin, String state);

    List<Pick> findByOriginAndOperation(String origin, String operation);

    @Query(value = "SELECT ID FROM STOCK_PICK WHERE PARCEL_LIST_NO = ?1 AND ORIGIN = ?2 AND OPERATION = 'receipt'", nativeQuery = true)
    List<Map> accordParcelListNoFind(String parcelListNo, String origin);

    @Query(value = "select t from Pick t where t.state in ('confirmed','locked')" +
            " and (t.origin like %:origin% or t.id like %:origin% " +
            " or exists (select 1 from PurchaseOrder f where f.id = t.origin and (f.supplierRef like %:origin% or f.origin like %:origin% or f.source like %:origin%) ))" +
            " and t.location.depot.dept.id in (:deptId) " +
            " and t.location.id in (:locationId) ")
    List<Pick> findPicksForPDA(@Param("origin") String origin, @Param("deptId") List<String> deptIds, @Param("locationId") List<String> locationIds);

    Pick findFirstByOriginAndOriginBusinessType(String origin, String originBusinessType);

    @Query(value = " SELECT C.ORIGIN CKDH, A.STATE RKDJZT, A.ID RKDH, B.QTY_DONE YSSL, B-.PRODUCT_ID SPID " +
            "   FROM STOCK_PICK A, STOCK_PICK_ITEM B, PUR_PURCHASE_ORDER C " +
            "  WHERE A.ID = B.PICK_ID AND A.ORIGIN = C.ID AND C.ORIGIN = :origin ", nativeQuery = true)
    List<Map> queryPICK(@Param("origin")String origin);

    @Query(value = "SELECT * FROM STOCK_PICK A " +
            " WHERE EXISTS (SELECT 1 FROM V_REDEMPTION_PICK WHERE ID = A.ID) " +
            "   AND NOT EXISTS (SELECT 1 FROM PUR_REDEMPTION WHERE SOURCE = A.ID AND STATE != 'delete') " +
            "   AND A.STATE = 'done' AND A.company_id in :companyId ", nativeQuery = true)
    Page<Pick> queryRedemptionPick(@Param("companyId") List<String> companyId, Pageable pageable);

    @Query(value = "SELECT * FROM STOCK_PICK A " +
            " WHERE EXISTS (SELECT 1 FROM V_REDEMPTION_PICK WHERE ID = A.ID) " +
            "   AND NOT EXISTS (SELECT 1 FROM PUR_REDEMPTION WHERE SOURCE = A.ID AND STATE != 'delete') " +
            "   AND A.STATE = 'done' AND A.id = :id AND A.company_id in :companyId ", nativeQuery = true)
    Page<Pick> queryRedemptionPickAppendCondition(@Param("id")String id, @Param("companyId") List<String> companyId, Pageable pageable);

    @Query(value = "SELECT * FROM STOCK_PICK A " +
            " WHERE EXISTS (SELECT 1 FROM V_REDEMPTION_PICK WHERE ID = A.ID) " +
            "   AND NOT EXISTS (SELECT 1 FROM PUR_REDEMPTION WHERE SOURCE = A.ID AND STATE != 'delete') " +
            "   AND A.STATE = 'done' AND A.id = :id ", nativeQuery = true)
    Page<Pick> queryRedemptionPickAppendConditionNotCompany(@Param("id")String id, Pageable pageable);

    @Query(value = "select id,name from soe_business_type where id in(SELECT origin_business_type from stock_pick GROUP  BY origin_business_type)",nativeQuery = true)
    List<Map> getPickType();

    Pick findByOrigin(@Param("origin") String origin);


    @Query("select sum(p.qtyDone) as qtyDone from PickItem p where p.pick.location.depot.dept.id = :deptId and p.pick.operation = :operation and p.pick.state = 'done'" +
            " and p.pick.dateDone > :startDate and p.pick.dateDone < :endDate ")
    Map<String, Object> findDonePick(@Param("deptId") String deptId, String operation, Date startDate, Date endDate);
}
